package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;


 public class DBClothing implements IFDBClothing{
    private  Connection con;
    /** Creates a new instance 
     * of DBEmployee */
    public DBClothing() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all clothing
    public ArrayList<Clothing> getAllClothing()
    {
        return miscWhere("");
    }
    //get one clothing having the ID
    public Clothing findClothing(int clothingID)
    {   String wClause = "  clothingID = '" + clothingID + "'";
        return singleWhere(wClause);
    }
      //find one clothing having the ID
    public Clothing searchClothingID(int attValue)
    {
        String wClause = "clothingID = '" + attValue + "'";
        System.out.println("Search Clothing: " + wClause);
        return singleWhere(wClause);
    }
     //insert a new clothing 
    public void insertClothing(Clothing clothing) throws SQLException
    {  
	   String query="INSERT INTO clothing(clothingID, colour, size)  VALUES("+
	  		clothing.getProductID()  + ",'"  +
		    clothing.getColour()  + "','"  +
                        clothing.getSize() + "')";
                     
      System.out.println("insert : " + query);		  					
      try{ 
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
		}
	 	catch(SQLException ex){
	 	  	System.out.println("Insert exception in clothing db: "+ex);
	   }
	}
	public void updateClothing(Clothing cloth) throws SQLException
	{
		Clothing clothObj  = cloth;
	  
		String query="UPDATE Clothing SET "+
		 	  "size ='"+ clothObj.getSize()+"', "+
		 	  "colour ='"+ clothObj.getColour()+"'" +
		 	  		"WHERE clothingID = "+clothObj.getProductID();
                System.out.println("Update query:" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
	}
	 
	private ArrayList<Clothing> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Clothing> list = new ArrayList<Clothing>();	
		
		String query =  buildQuery(wClause);
  
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
		     	 Clothing clothingObj = new Clothing();
			 clothingObj = buildClothing(results);	
                         list.add(clothingObj);	
			}
			stmt.close();
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	private Clothing singleWhere(String wClause)
	{
		ResultSet results;
		Clothing clothingObj = new Clothing();
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            clothingObj = buildClothing(results);
                            stmt.close();
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return clothingObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Clothing";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object
	private Clothing buildClothing(ResultSet results)
      {   Clothing clothingObj = new Clothing();
          try{
                clothingObj.setProductID(results.getInt(1));
                clothingObj.setColour(results.getString(2));
                clothingObj.setSize(results.getString(3));
               
          }
         catch(Exception e)
         {
             System.out.println("error in building the clothing object");
         }
         return clothingObj;
      } 
}  